﻿<ViewDataFormTemplateSpec 
	xmlns="bb_appfx_viewdataformtemplate" 
	xmlns:common="bb_appfx_commontypes" 
	ID="b6029e7c-ac65-48d3-abe5-2fa6cc47bf23" 
	Name="Get Reciprocal ID for a constituent"
	Description="Used to get Reciprocal ID"
	Author="Blackbaud Professional Services"
	DataFormInstanceID="a268bdf0-1715-4bd0-a33e-c71fe63b6c09"
	RecordType="Constituent"
	common:SecurityUIFolder="Service Bus Adapter"
	>

	<!-- define the SP that returns the data used by the form -->
	<SPDataForm SPName="USP_DATAFORMTEMPLATE_VIEW_RECIPROCALID">
		<common:CreateProcedureSQL>
			<![CDATA[
create procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RECIPROCALID
(
	@ID uniqueidentifier,
	@DATALOADED bit = 0 output,
	@RECIPROCALID uniqueidentifier = null output
)
as
	set nocount on;
	
	-- be sure to set this, in case the select returns no rows
	set @DATALOADED = 0;
	
	-- populate the output parameters, which correspond to fields on the form.  Note that
	-- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system
	-- will display a "no data loaded" message.
  
  ---------
  with Reciprocaltbl as 
  (SELECT TOP 1 COUNT(relt.DESCRIPTION + recip.DESCRIPTION) cnt
          ,relt.ID as id1
          ,recip.ID as id2
          ,relt.DESCRIPTION as relationship
          ,recip.DESCRIPTION
   from
   constituent con1
   inner join
   relationship rel
   on con1.id = rel.relationshipconstituentid
   inner join
   RELATIONSHIPTYPECODE relt
   on relt.ID = rel.RELATIONSHIPTYPECODEID
   inner join
   CONSTITUENT con2
   on con2.ID = rel.RECIPROCALCONSTITUENTID
   inner join
   RELATIONSHIPTYPECODE recip
   on recip.ID = rel.RECIPROCALTYPECODEID
   where recip.DESCRIPTION!='<Not Specified>' and relt.DESCRIPTION!='<Not Specified>'
   AND relt.ID=@ID
   GROUP BY  relt.ID,recip.ID,relt.DESCRIPTION,recip.DESCRIPTION
   order by 1 desc)
   select @RECIPROCALID=Reciprocaltbl.id2 
         ,@DATALOADED=1
   from Reciprocaltbl

  ---------
  
	return 0;
			]]>
		</common:CreateProcedureSQL>
	</SPDataForm>

	<!-- describe fields on the form, which correspond to parameters on the SP.  Note that system paramters 
	like the context @ID, @DATALOADED, and @CURRENTAPPUSERID need not be listed. -->
	<common:FormMetaData FixedDialog="true">
		<common:FormFields>
			<common:FormField FieldID="RECIPROCALID" Caption="RECIPROCALID" DataType="String" MaxLength="50" />
				</common:FormFields>
	</common:FormMetaData>

</ViewDataFormTemplateSpec>